﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Npgsql;
using Ortodoctor.Componentes;
using System.Data;

namespace Ortodoctor.modelo
{
    public class modeloCredito
    {
        //string connectionstring = ("Server=186.202.152.24;Port=5432;User Id=ortodoctor1;Password=DycbqDfHLI;Database=ortodoctor1;MinPoolSize=5;CommandTimeout=60;");
        string connectionstring = ("Server=localhost;Port=5432;User Id=postgres;Password=postgres;Database=ortodoctor;MinPoolSize=5;CommandTimeout=60;"); NpgsqlCommand com;
        NpgsqlConnection conn;
        NpgsqlDataAdapter da;
        //conexao con = new conexao();
        public modeloCredito()
        {
            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
        }

        public void insert(int credito, int id_cliente)
        {
            try
            {
                ////con.open();
                String sql = "INSERT INTO creditos(credito, id_cliente, data_credito) VALUES (:p1, :p2, :p3)";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", credito);
                com.Parameters.AddWithValue("p2", id_cliente);
                com.Parameters.AddWithValue("p3", DateTime.Now.ToString("dd/MM/yyyy"));
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { }
            finally
            {
                conn.Close();
            }
        }

        public string getDate(int id_cliente)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT data_credito FROM creditos where id_cliente=" + id_cliente;
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            if(dt == null || dt.Rows.Count == 0)
                return "";

            return Convert.ToDateTime(dt.Rows[0]["data_credito"]).ToString("dd/MM/yyyy");
        }

        public int getCredito(int id_cliente)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT credito FROM creditos where id_cliente=" + id_cliente;
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            if (dt == null || dt.Rows.Count == 0)
                return -1;

            return Convert.ToInt32(dt.Rows[0]["credito"]);
        }

        internal void credita(int credito, int id_cliente)
        {
            try
            {
                string date = getDate(id_cliente);
                if (Convert.ToInt32(date.Split('/')[1]) + Convert.ToInt32(date.Split('/')[2]) * 12 <= DateTime.Now.Year * 12 + DateTime.Now.Month)
                {
                    //con.open();
                    String sql = "update creditos set credito=:p1, data_credito=:p2 where id_cliente = :p3";
                    NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                    com.Parameters.AddWithValue("p1", credito);
                    com.Parameters.AddWithValue("p2", DateTime.Now.ToString("dd/MM/yyyy"));
                    com.Parameters.AddWithValue("p3", id_cliente);
                    com.ExecuteNonQuery();
                }
            }
            catch (Exception ex) {
                string date = getDate(id_cliente);
                if (Convert.ToInt32(date.Split('/')[1]) + Convert.ToInt32(date.Split('/')[2]) * 12 <= DateTime.Now.Year * 12 + DateTime.Now.Month)
                {
                    conn.Open();
                    String sql = "update creditos set credito=:p1, data_credito=:p2 where id_cliente = :p3";
                    NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                    com.Parameters.AddWithValue("p1", credito);
                    com.Parameters.AddWithValue("p2", DateTime.Now.ToString("dd/MM/yyyy"));
                    com.Parameters.AddWithValue("p3", id_cliente);
                    com.ExecuteNonQuery();
                }
            }
            finally
            {
                conn.Close();
            }
        }

        internal void debita(int id_cliente, int debito)
        {
            int debitar = getDebito(id_cliente);
            try
            {
                conn.Open();
                String sql = "update creditos set debito=:p1, data_credito=:p2 where id_cliente = :p3";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", debitar + debito);
                com.Parameters.AddWithValue("p2", DateTime.Now.ToString("dd/MM/yyyy"));
                com.Parameters.AddWithValue("p3", id_cliente);
                com.ExecuteNonQuery();
            }
            catch
            {
                String sql = "update creditos set debito=:p1, data_credito=:p2 where id_cliente = :p3";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", debitar + debito);
                com.Parameters.AddWithValue("p2", DateTime.Now.ToString("dd/MM/yyyy"));
                com.Parameters.AddWithValue("p3", id_cliente);
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        internal int getDebito(int id_cliente)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "select (debito) as saldo from creditos where id_cliente=" + id_cliente;
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            catch (Exception ex) { }
            finally
            {
                conn.Close();
            }

            if (dt == null || dt.Rows.Count == 0)
                return 0;
            else
                return Convert.ToInt32(dt.Rows[0]["saldo"]);
        }

        internal bool getSaldoSoma(int id_cliente)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = @"select status from parcelas 
                                            inner join procedimento_cliente using(id_procedimento_cliente) 
                                where id_cliente = " + id_cliente + " order by data_vencimento limit(1)";
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            catch (Exception ex) { }
            finally
            {
                conn.Close();
            }

            if (dt == null || dt.Rows.Count == 0)
                return false;
            else
            {
                dt.DefaultView.RowFilter = "[status] like '%PAGO%'";
                return dt.DefaultView.ToTable().Rows.Count == 1 ? true : false;
            }
        }

        internal bool getJaSomou(int id_cliente)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = @"select count(*) as cont from configuracoes where parametro = 'SOMA_CREDITO' and valor = '" + id_cliente + "'";
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            catch (Exception ex) { }
            finally
            {
                conn.Close();
            }

            if (dt == null || dt.Rows.Count == 0)
                return false;
            else
                return dt.Rows[0]["cont"].ToString().Equals("0") ? true : false;
        }

        internal void setConfiguracao(int id_cliente)
        {
            try
            {
                //con.open();
                String sql = "insert into configuracoes(parametro, valor) values ('SOMA_CREDITO', '" + id_cliente + "')";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { }
            finally
            {
                conn.Close();
            }
        }
    }
}